package service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

import util.MD5;

import db.DBConn;
import entities.UserEntity;

public class UserService {
	// 得到用户名为 *** 的用户
	public static UserEntity getUser(String userName) throws SQLException {
		UserEntity user = new UserEntity();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_user where user_name like '"
				+ userName + "'";
		// System.out.println(sql);
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			user.setId(rs.getInt("user_id"));
			user.setName(rs.getString("user_name"));
			user.setAddr(rs.getString("user_addr"));
			user.setEmail(rs.getString("user_email"));
			user.setPassword(rs.getString("user_password"));
			user.setTel(rs.getString("user_tel"));
			user.setRegTime(rs.getTimestamp("user_regtime").toString());
			user.setSex(rs.getString("user_sex"));
			user.setCampus(rs.getString("user_campus"));
			user.setHeadImgUrl(rs.getString("user_headImg"));
			user.setOrigin(rs.getString("user_origin"));
		}
		rs.close();
		conn.close();
		return user;
	}

	// 检查是否有某一用户
	public static boolean checkUser(String userName) throws SQLException {
		Connection conn = DBConn.getConn();
		boolean result = false;
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_user where user_name like '"
				+ userName + "'";
		System.out.println(sql);
		ResultSet rs = stmt.executeQuery(sql);
		if (rs.first()) {
			result = true;
		}
		rs.close();
		conn.close();
		return result;
	}

	// 检查是否有某一用户
	public static boolean checkUserByEmail(String email) throws SQLException {
		Connection conn = DBConn.getConn();
		boolean result = false;
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_user where user_email like '" + email
				+ "'";
		System.out.println(sql);
		ResultSet rs = stmt.executeQuery(sql);
		if (rs.first()) {
			result = true;
		}
		rs.close();
		conn.close();
		return result;
	}

	// 检查是否有其他用户使用了某个邮箱
	public static boolean checkUserByEmail(String userName, String email)
			throws SQLException {
		Connection conn = DBConn.getConn();
		boolean result = false;
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_user where user_name not like '"
				+ userName + "' and  user_email like '" + email + "'";
		System.out.println(sql);
		ResultSet rs = stmt.executeQuery(sql);
		if (rs.first()) {
			result = true;
		}
		rs.close();
		conn.close();
		return result;
	}

	// 检查是否有某一用户
	public static boolean checkUserByUserName(String userName)
			throws SQLException {
		Connection conn = DBConn.getConn();
		boolean result = false;
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_user where user_name like '"
				+ userName + "'";
		System.out.println(sql);
		ResultSet rs = stmt.executeQuery(sql);
		if (rs.first()) {
			result = true;
		}
		rs.close();
		conn.close();
		return result;
	}

	// 检查是否有某一用户
	public static boolean checkUserByPhone(String phone) throws SQLException {
		Connection conn = DBConn.getConn();
		boolean result = false;
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_user where user_tel like '" + phone
				+ "'";
		System.out.println(sql);
		ResultSet rs = stmt.executeQuery(sql);
		if (rs.first()) {
			result = true;
		}
		rs.close();
		conn.close();
		return result;
	}

	// 验证用户登录
	public static String confirm(String userName, String password)
			throws SQLException {
		Connection conn = DBConn.getConn();
		String result = "failed";
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_user where (user_name = '" + userName
				+ "' or user_email = '" + userName + "'  or user_tel = '"
				+ userName + "' ) and user_password = '" + password + "'";
		System.out.println(sql);
		ResultSet rs = stmt.executeQuery(sql);
		if (rs.first()) {
			result = rs.getString("user_name");
		}
		rs.close();
		conn.close();
		return result;
	}

	// 添加用户
	public static boolean addUser(UserEntity user) throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String regtime = sdf.format(new Date());
		String sql = "insert into shop_user values(null,'" + user.getName()
				+ "','" + user.getEmail() + "','" + user.getHeadImgUrl()
				+ "','" + user.getPassword() + "','" + user.getAddr() + "','"
				+ user.getCampus() + "','" + user.getTel() + "','"
				+ user.getOrigin() + "','" + user.getSex() + "','" + regtime
				+ "',0,'" + regtime + "',0,'" + user.getHeadImgUrl() + "','"
				+ user.getHeadImgUrl() + "')";
		System.out.println(sql);
		int result = stmt.executeUpdate(sql);
		conn.close();
		return result > 0 ? true : false;
	}

	// 修改用户 的基本信息
	public static String changeUser(String userName, String sex, String addr,
			String email, String campus, String other) throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "update shop_user set user_sex='" + sex
				+ "' , user_addr='" + addr + "', user_email='" + email
				+ "', user_campus='" + campus + "'  where user_name like '"
				+ userName + "'";
		System.out.println(sql);
		int rs = stmt.executeUpdate(sql);
		stmt.close();
		conn.close();
		return rs == 0 ? "failed" : "success";
	}

	// 修改用户 的密码
	public static String changeUserPassword(String userName, String password)
			throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "update shop_user set user_password ='" + password
				+ "'  where user_name like '" + userName + "'";
		System.out.println(sql);
		int rs = stmt.executeUpdate(sql);
		stmt.close();
		conn.close();
		return rs == 0 ? "failed" : "success";
	}

	// 修改用户 的图片
	public static String changeUserHead(String userName, String headImg)
			throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "update shop_user set user_headImg='" + headImg
				+ "'  where  user_name like '" + userName + "'";
		System.out.println(sql);
		int rs = stmt.executeUpdate(sql);
		conn.close();
		return rs == 0 ? "failed" : "success";
	}

	// 修改用户 的电话
	public static String changeUserPhone(String userName, String phone)
			throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "update shop_user set user_tel ='" + phone
				+ "'  where user_name like '" + userName + "'";
		System.out.println(sql);
		int rs = stmt.executeUpdate(sql);
		stmt.close();
		conn.close();
		return rs == 0 ? "failed" : "success";
	}

	public static void main(String args[]) throws SQLException {
		//System.out.println(confirm("liwentao", "fds"));
		String userName = "test";
		String password = "test";
		String tel = "13159033865";
		String addr = "华大北区紫荆园35#A711";

 	    UserEntity user = new UserEntity();
		 user.setAddr(addr);
		 user.setName(userName);
		 user.setPassword(MD5.Encrypt(password));
		 // user.setRegTime(new Date());
		 user.setTel(tel);
		// System.out.println(addUser(user));
		//System.out.println(changeUserPhone("liwentao", "12345678900"));
		// String email = "1072435437@qq.com";
		// System.out.println(email.substring(0, email.indexOf("@")));
		// System.out.println(confirm("liwentao", MD5.Encrypt("liwentao")));
		// System.out.println(changeUserHead("娜姐", "user/head/user_1.png"));
	}
}
